package com.cpi.cornpp.purchaseorder.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.purchase.program.exception.PurchaseProgramException;
import com.cpi.cornpp.purchaseorder.dto.PurchaseOrderCostVersionDTO;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class PurchaseOrderCostVersionDAO extends DBDAO {
	private static final String SQL_INSERT_PURCHASE_ORDER_COST_VERSION = "INSERT INTO purchases_orders_costs_versions (cost_element_id, purchase_order_version_id, amount) VALUES (?, ?, ?)";//$NON-NLS-1$
	private static final String SQL_UPDATE_PURCHASE_ORDER_COST_VERSION = "UPDATE purchases_orders_costs_versions SET amount = ?";//$NON-NLS-1$
	private static final String SQL_DELETE_PURCHASE_ORDER_COST_VERSION = "DELETE FROM purchases_orders_costs_versions";//$NON-NLS-1$
	private static final String SQL_SELECT_PURCHASE_ORDER_COST_VERSION = "SELECT purchases_orders_costs_versions.cost_element_id, purchase_order_version_id, amount, short_name FROM purchases_orders_costs_versions  inner join costs_elements on purchases_orders_costs_versions.cost_element_id = costs_elements.cost_element_id";//$NON-NLS-1$
	private static final String SQL_DELETE_PURCHASE_ORDER_COST_VERSION_BY_COST_ELEMENT = "DELETE FROM purchases_orders_costs_versions where purchase_order_version_id  = ? and cost_element_id = ?";//$NON-NLS-1$
	private static final String SQL_EXISTS_PURCHASE_ORDER_COST = "select amount from purchases_orders_costs_versions where purchase_order_version_id = ? and cost_element_id = ?";
	
	public boolean existsCharge(PurchaseOrderCostVersionDTO purchaseOrderCostVersionDTO)throws PurchaseProgramException{
		boolean exists = false;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try{
			preparedStatement = prepareSQL(SQL_EXISTS_PURCHASE_ORDER_COST, dsName);
			preparedStatement.setInt(1, purchaseOrderCostVersionDTO.getPurchaseOrderVersionId().intValue());
			preparedStatement.setInt(2, purchaseOrderCostVersionDTO.getCostElementId().intValue());
			resultSet = executeQuery(preparedStatement);
			exists = resultSet.next();
		} catch(PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			purchaseProgramException = new PurchaseProgramException(poolException, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_DELETE);
			poolException.printStackTrace(System.err);
			throw purchaseProgramException;
		} catch(SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			purchaseProgramException = new PurchaseProgramException(sqlException, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_DELETE);
			sqlException.printStackTrace(System.err);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			} catch(SQLException sqlException){
				sqlException.printStackTrace(System.out);
			}
		}
		return exists;
	}
	
	public void delete(PurchaseOrderCostVersionDTO purchaseOrderCostVersionDTO)throws PurchaseProgramException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_DELETE_PURCHASE_ORDER_COST_VERSION_BY_COST_ELEMENT, dsName);
			preparedStatement.setInt(1, purchaseOrderCostVersionDTO.getPurchaseOrderVersionId().intValue());
			preparedStatement.setInt(2, purchaseOrderCostVersionDTO.getCostElementId().intValue());
			executeUpdate(preparedStatement);
		} catch(PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			purchaseProgramException = new PurchaseProgramException(poolException, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_DELETE);
			poolException.printStackTrace(System.err);
			throw purchaseProgramException;
		} catch(SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			purchaseProgramException = new PurchaseProgramException(sqlException, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_DELETE);
			sqlException.printStackTrace(System.err);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			} catch(SQLException sqlException){
				sqlException.printStackTrace(System.out);
			}
		}
	}
	
	public int insertPurchaseOrderCostVersion(PurchaseOrderCostVersionDTO pcvDTO) throws PurchaseProgramException{
		int insertedRows = -1;
		PreparedStatement pstm = null;
		
		try{
			
			if (pcvDTO != null){
				pstm = prepareSQL(SQL_INSERT_PURCHASE_ORDER_COST_VERSION, dsName);
				
				pstm.setInt(1, pcvDTO.getCostElementId().intValue());
				pstm.setInt(2, pcvDTO.getPurchaseOrderVersionId().intValue());
				pstm.setDouble(3, pcvDTO.getAmount().doubleValue());
				
				insertedRows = executeInsert(pstm);
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_INSERT);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_INSERT);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return insertedRows;
	}
	
	public int updatePurchaseOrderCostVersionById(PurchaseOrderCostVersionDTO pcvDTO) throws PurchaseProgramException{
		int updatedRows = -1;
		PreparedStatement pstm = null;
		StringBuffer sql = null;
		
		try{
			
			if (pcvDTO != null){
				sql = new StringBuffer(SQL_UPDATE_PURCHASE_ORDER_COST_VERSION);
				
				sql.append(SQL_WHERE);
				sql.append(PurchaseOrderCostVersionDTO.PURCHASE_ORDER_VERSION_ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				sql.append(SQL_AND);
				sql.append(PurchaseOrderCostVersionDTO.COST_ELEMENT_ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setDouble(1, pcvDTO.getAmount().doubleValue());
				pstm.setInt(2, pcvDTO.getPurchaseOrderVersionId().intValue());
				pstm.setInt(3, pcvDTO.getCostElementId().intValue());
				
				updatedRows = executeUpdate(pstm);
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return updatedRows;
	}
	
	public int deletePurchaseOrderCostVersionByPovId(Integer povId) throws PurchaseProgramException{
		int deletedRows = -1;
		PreparedStatement pstm = null;
		StringBuffer sql = null;
		
		try{
			
			if (povId != null){
				sql = new StringBuffer(SQL_DELETE_PURCHASE_ORDER_COST_VERSION);
				
				sql.append(SQL_WHERE);
				sql.append(PurchaseOrderCostVersionDTO.PURCHASE_ORDER_VERSION_ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setInt(1, povId.intValue());
				
				deletedRows = executeUpdate(pstm);
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return deletedRows;
	}
	
	public List getPurchaseOrderCostsVersionsByPovId(Integer povId) throws PurchaseProgramException{
		List purchaseOrderCostVersions = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;
		
		try{
			
			if (povId != null){
				sql = new StringBuffer(SQL_SELECT_PURCHASE_ORDER_COST_VERSION);
				
				sql.append(SQL_WHERE);
				sql.append(PurchaseOrderCostVersionDTO.PURCHASE_ORDER_VERSION_ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setInt(1, povId.intValue());
				
				rs = executeQuery(pstm);
				purchaseOrderCostVersions = mapRows(rs);
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return purchaseOrderCostVersions;
	}
	
	private PurchaseOrderCostVersionDTO mapRow(ResultSet rs, int index) throws SQLException{
		PurchaseOrderCostVersionDTO pcvDTO = null;
		
		if ((rs != null) && (rs.absolute(index))){
			pcvDTO = new PurchaseOrderCostVersionDTO();
			
			pcvDTO.setCostElementId((Integer) rs.getObject(PurchaseOrderCostVersionDTO.COST_ELEMENT_ID));
			pcvDTO.setCostElementCode( rs.getString(PurchaseOrderCostVersionDTO.COST_ELEMENT_CODE));
			pcvDTO.setPurchaseOrderVersionId((Integer) rs.getObject(PurchaseOrderCostVersionDTO.PURCHASE_ORDER_VERSION_ID));
			pcvDTO.setAmount(new Double(rs.getDouble(PurchaseOrderCostVersionDTO.AMOUNT)));
		}
		
		return pcvDTO;
	}
	
	private List mapRows(ResultSet rs) throws SQLException{
		List purchaseOrderCostVersions = null;
		
		purchaseOrderCostVersions = new ArrayList(0);
		
		if (rs != null){
			
			for (; rs.next(); ){
				purchaseOrderCostVersions.add(mapRow(rs, rs.getRow()));
			}
		}
		
		return purchaseOrderCostVersions;
	}
}